package com.wy.jsnm; /**

流程: 接口平台，1.读取数据库数据到接口平台服务器。获取到的图片存在本地 。再上传到nginx 服务器.
2.当从数据库获取到一张图片后,直接上传到nginx服务器。或者先将图片存放到List中，。

3.直接存到接口所在的服务器，通过nginx 转发到本服务器.
 这里要做的：1.读取BLOB,转为jpg存放到本地,2.将路径字符串转为sql语句,update ,set

HeadImgPO:
{
	private BLOB ZP;
	private String filePath;
}

*/


import java.io.*;
import java.sql.*;
import java.util.*;
public class HeadImgUtil{

	private static String COUNT_SQL = "SELECT COUNT(*) num FROM YDPT_XSZP";//获得表中的数据量.
	
	private String sql2 = "SELECT * FROM YDPT_XSZP WHERE ROWNUM < ?";//todo 读取大对象,用分页的形式

	private String requestURL = "http://app.jsahvc.edu.cn/headImg/";//访问地址，为nginx服务器的域名,可参考下载页面的下载文件配置的路径

	private String sql = "SELECT *\n" +
			"  FROM (SELECT ROWNUM AS rowno, t.*\n" +
			"          FROM YDPT_XSZP t\n" +
			"         WHERE ROWNUM <= ?) table_alias\n" +
			" WHERE table_alias.rowno >= ?";

	private String getXhSql = "SELECT XH\n" +
			"  FROM (SELECT ROWNUM AS rowno, t.*\n" +
			"          FROM YDPT_XSZP t\n" +
			"         WHERE ROWNUM <= ?) table_alias\n" +
			" WHERE table_alias.rowno >= ?";


	private String updateSql = "UPDATE t_userbase set headImg = ? WHERE userid =(SELECT l.userid FROM t_userlogin l, t_userbase u" +
			" WHERE l.userid = u.userid AND l.username = ?)";
	private String savePath = "/usr/local/lantu/nginx/html/headImg/";

	private int pageNum = 1000;

	//从数据库读取图片
	public void downloadImg(){
		/**获取mobileCampus数据库连接，存储headImg,批量提交*/
		Connection mysqlConn = getConn2("mysql");//存储路径用
		Connection oracleConn = getConn2("oracle");//获取图片用
		/**END*/

		List<HeadImgPo> list;
		List<HeadImgPo> filePathList = new ArrayList<>();
		ResultSet rs = null;
		Statement st;
		PreparedStatement ps = null;
		HeadImgPo headImgPo;
		FileWriter fw = null;
		try {
			/**事务存储路径*/
			mysqlConn.setAutoCommit(false);//关闭mysql自动提交
			st = mysqlConn.createStatement();
			/***/
			ps = oracleConn.prepareStatement(COUNT_SQL);
			rs = ps.executeQuery();
			rs.next();
			int totalPage = rs.getInt("num") / pageNum + 1;
			System.out.println("总共" + totalPage + "页");
			ps.close();
			rs.close();
			//分页处理
			for (int i = 1; i <= totalPage; i++) { //todo  i < = totalpage
				System.out.println("************正在执行第" + i + "页数据，还有" + (totalPage - 84) +"页数据************");
				list = new ArrayList<>();
				ps = oracleConn.prepareStatement(getXhSql);
				System.out.println(getXhSql);
				ps.setInt(1,i * pageNum);
				ps.setInt(2,(i - 1) * pageNum);
				rs = ps.executeQuery();
				while (rs.next()) {
					headImgPo = new HeadImgPo();
					headImgPo.setXgh(rs.getString("xh"));
					headImgPo.setRequestPath(requestURL + rs.getString("xh") + ".jpg");
					savePath = "F:/pic/";
					headImgPo.setSavePath(savePath + rs.getString("xh") + ".jpg");
					//headImgPo.setBlob(rs.getBlob("zp"));//todo 只是获取学号不用查询BLOB
					list.add(headImgPo);
					filePathList.add(headImgPo);
				}
				System.out.println("总共获得" + list.size() + "条数据");
				//saveImg2(list);//保存图片  todo 只是获取学号不需要存储

			}
			/**用于生成sql文件*/
			File file=new File("HeadImg.sql");
			fw = new FileWriter(file);
			StringBuilder temp = new StringBuilder();
			/***/
			/**
			 * update t_userbase set headimage = 'http://app.jsahvc.edu.cn/headImg/201310006.jpg'
			 	where userid = (select userid from t_userlogin where username = 201310006)

			 *
			 */
			//将路径转换为sql语句，加入sql缓存
			for (HeadImgPo imgPo : filePathList){
				String updateSql = "update mobilecampus.t_userbase set headimage = '" + imgPo.getRequestPath() +"'" +
						" where userid = (select userid from t_userlogin where username = " + imgPo.getXgh()+");";
				temp.append(updateSql);
				temp.append("\n");
				//st.addBatch(updateSql);//加入数据库执行缓存
			}
			fw.append(temp);
			System.out.println("生成的sql长度" + temp.length());
//			mysqlConn.commit();//提交事务
			System.out.println("*************执行完成**************");
		} catch (Exception e) {
			e.printStackTrace();
//			try {
//				mysqlConn.rollback();//回滚事务
//			} catch (SQLException e1) {
//				e1.printStackTrace();
//			}
		} finally {
				try {
					if (rs != null)
						rs.close();
					if (ps != null)
						ps.close();
					mysqlConn.close();
					oracleConn.close();
					try {
						fw.flush();
						fw.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}
	
	//保存图片到本地
	public void saveImg(List<HeadImgPo> list) throws IOException, SQLException {
		DataOutputStream dos = null;
		InputStream fis = null;
		HeadImgPo headImgPo = null;
		Blob blob;
		int out;
		try {
			for (int i = 0; i < list.size(); i++) {
				headImgPo = list.get(i);
				blob = headImgPo.getBlob();
				dos = new DataOutputStream(new FileOutputStream(headImgPo.getSavePath()));
				fis = blob.getBinaryStream();

				byte[] outByte = new byte [1024 * 2];
				// 将blob对象输入流写入本地输出流中
				while ((out = fis.read(outByte)) != -1) {
					dos.write(out);
				}
			}
		}catch (IOException e){
			e.printStackTrace();
		}catch (SQLException e){
			e.printStackTrace();
		}finally {
			try {
				if (fis != null)
					fis.close();
				if (dos != null) {
					dos.flush();
					dos.close();
				}
			}catch (IOException e){
				e.printStackTrace();
			}
		}
	}

	public void saveImg2(List<HeadImgPo> list){
		for (HeadImgPo po : list){
			String path = po.getSavePath();
			File file2 = new File(path);
			OutputStream outputStream = null;
			try {
				outputStream = new FileOutputStream(file2);
				outputStream.write(po.getBlob().getBytes(1,(int)po.getBlob().length()));
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}
	//将文件路径存到数据库
	public void saveFilePath(List<HeadImgPo> list){
		/**
		 * 拼接 update 的sql语句. 提交执行.
		 * commit
		 * */
	}
	
	/**数据库连接*/
	private Connection getConn2(String dbType){
		String path = HeadImgUtil.class.getClassLoader().getResource("config.properties").getPath();
		InputStream in = null;
		Connection conn = null;
		Properties pro = new Properties();
		try {
			in = new FileInputStream(path);
			pro.load(in);

		String className = "";
		String url = "";
		String username = "";
		String password = "";
		if (dbType.equals("mysql")) {
			className = pro.getProperty("jdbc.driver");
			url = pro.getProperty("jdbc.url");
			username = pro.getProperty("jdbc.user");
			password = pro.getProperty("jdbc.pwd");
		}else if (dbType.equals("oracle")){
			className = pro.getProperty("jdbc_oracle.driver");
			url = pro.getProperty("jdbc_oracle.url");
			username = pro.getProperty("jdbc_oracle.user");
			password = pro.getProperty("jdbc_oracle.pwd");
		}
		try {
			Class.forName(className);
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e){
			e.printStackTrace();
		}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}catch (IOException e){
			e.printStackTrace();
		}finally {
			try {
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			pro.clone();
		}
		return conn;
	}

	public static void main(String[] args){
		HeadImgUtil headImgUtil = new HeadImgUtil();
		headImgUtil.downloadImg();
	}
}